﻿using System;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;

namespace HEFramework
{
    public class XssfUtils
    {
        public static IWorkbook OpenWorkBook(string _file)
        {
            IWorkbook wb = null;
            FileStream fs = System.IO.File.Open(_file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            wb = WorkbookFactory.Create(fs);
            fs.Close();
            return wb;
        }

        public static Dictionary<string, List<List<string>>> GetXssfData(string _file, Func<string, bool> _sheetRequire = null)
        {
            var xssf = OpenWorkBook(_file);
            var dic = GetXssfData(xssf, _sheetRequire);
            xssf.Close();
            return dic;
        }

        public static Dictionary<string, List<List<string>>> GetXssfData(IWorkbook wb, Func<string, bool> _sheetRequire = null)
        {
            Dictionary<string, List<List<string>>> result = null;
            if (wb != null)
            {
                result = new Dictionary<string, List<List<string>>>();
                for (int i = 0; i < wb.NumberOfSheets; i++)
                {
                    if (_sheetRequire == null || _sheetRequire(wb.GetSheetName(i)))
                    {
                        ISheet sheet = wb.GetSheetAt(i);
                        result.Add(sheet.SheetName, GetSheetData(sheet));
                    }
                }
            }

            return result;
        }

        public static List<List<string>> GetSheetData(ISheet _sheet)
        {
            List<List<string>> sheetList = null;
            if (_sheet != null)
            {
                sheetList = new List<List<string>>();

                int startRow = 0; //sheet.FirstRowNum
                int endRow = _sheet.LastRowNum;
                List<string> rowList;
                IRow row;
                IRow FirstRow = _sheet.GetRow(0);

                for (int i = startRow; i <= endRow; i++)
                {
                    row = _sheet.GetRow(i);
                    rowList = GetRowData(row);
                    if (rowList == null)
                    {
                        sheetList.Add(new List<string>());
                    }
                    else
                    {
                        sheetList.Add(rowList);
                    }
                }
            }

            return sheetList;
        }

        public static List<string> GetRowData(ISheet _sheet, int _rowIndex)
        {
            return GetRowData(_sheet?.GetRow(_rowIndex));
        }

        public static List<string> GetRowData(IRow _row)
        {
            List<string> result = null;
            if (_row != null)
            {
                result = new List<string>();
                int startColumn = 0;
                int endColumn = _row.LastCellNum;
                for (int i = startColumn; i <= endColumn; i++)
                {
                    result.Add(GetCellString(_row.GetCell(i)));
                }
            }

            return result;
        }

        public static string GetCellString(IRow _row, int _column)
        {
            return GetCellString(_row?.GetCell(_column));
        }

        public static string GetCellString(ICell _cell)
        {
            if (_cell != null)
            {
                switch (_cell.CellType)
                {
                    case CellType.Unknown:
                        return "";
                    case CellType.Numeric:
                        return _cell.NumericCellValue.ToString();
                    case CellType.String:
                        return _cell.StringCellValue;
                    case CellType.Formula:
                        try
                        {
                            return _cell.StringCellValue;
                        }
                        catch (Exception e)
                        {
                            return _cell.NumericCellValue.ToString();
                        }
                    case CellType.Blank:
                        return "";
                    case CellType.Boolean:
                        return _cell.BooleanCellValue.ToString();
                    case CellType.Error:
                        return "";
                    default:
                        return "";
                }
            }
            else
            {
                return "";
            }
        }
    }
}